/**
 * 
 */
package com.java.test.user.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.java.test.datacenter.dto.PayInmentInfo;
import com.java.test.datacenter.dto.PaymentInfo;
import com.java.test.datacenter.dto.SumAccountInfo;
import com.java.test.user.dto.DepositInfo;
import com.java.test.user.dto.ReflectInfo;
import com.java.test.util.PageInfo;

/**
 * @author Administrator
 *
 */
@Component
public class DepositMapper {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int selectdepositCount(DepositInfo info) {
		String sql = "select count(*) from member_deposit where 1=1";

		if (info.getStartTime() != null && !"".equals(info.getStartTime())) {
			sql += " and deposit_time>='" + info.getStartTime() + "'";
		}

		if (info.getEndTime() != null && !"".equals(info.getEndTime())) {
			sql += " and deposit_time<'" + info.getEndTime() + "'";
		}

		if (info.getCurDate() != null && !"".equals(info.getCurDate())) {
			sql += " and TO_DAYS(deposit_time)=TO_DAYS('" + info.getCurDate() + "')";
		}
		if (info.getDepositChannel() != 0) {
			sql += " and deposit_channel ='" + info.getDepositChannel() + "'";
		}
		if (info.getStatus() != 0) {
			sql += " and status='" + info.getStatus() + "'";
		}

		if (info.getGameAccount() != null && !"".equals(info.getGameAccount())) {
			sql += " and game_account like '%" + info.getGameAccount() + "%'";
		}

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? 0 : i;

	}

	public List<DepositInfo> selectdepositList(PageInfo<DepositInfo> page, DepositInfo info) {
		String sql = "select * from member_deposit where 1=1";

		if (info.getStartTime() != null && !"".equals(info.getStartTime())) {
			sql += " and deposit_time>='" + info.getStartTime() + "'";
		}

		if (info.getEndTime() != null && !"".equals(info.getEndTime())) {
			sql += " and deposit_time<'" + info.getEndTime() + "'";
		}

		if (info.getCurDate() != null && !"".equals(info.getCurDate())) {
			sql += " and TO_DAYS(deposit_time)=TO_DAYS('" + info.getCurDate() + "')";
		}
		if (info.getDepositChannel() != 0) {
			sql += " and deposit_channel ='" + info.getDepositChannel() + "'";
		}
		if (info.getStatus() != 0) {
			sql += " and status='" + info.getStatus() + "'";
		}

		if (info.getGameAccount() != null && !"".equals(info.getGameAccount())) {
			sql += " and game_account like '%" + info.getGameAccount() + "%'";
		}

		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();

		sql += " order by audit_time desc limit ?,?";
		List<DepositInfo> list = jdbcTemplate.query(sql, new DepositInfo(), start, limit);
		return list;
	}

	public List<DepositInfo> selectdepositlistcodeSize(LocalDateTime startTime, LocalDateTime endTime, String account) {
		String sql = "select * from member_deposit where game_account='" + account + "'";

		if (startTime != null) {
			sql += " and deposit_time>='" + startTime + "'";
		}

		if (endTime != null) {
			sql += " and deposit_time<'" + endTime + "'";
		}

		List<DepositInfo> list = jdbcTemplate.query(sql, new DepositInfo());
		return list;
	}

	public void deletedepositById(int[] ids) {
		String sql = "delete from examine_deposit where id =?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return ids.length;
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			@SuppressWarnings("static-access")
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids[i]);
			}
		});
	}

	public void plinsertdepositInfo(List<DepositInfo> list) {
		String sql = "INSERT INTO `member_deposit` " + "(`game_account`, `deposit_amount`, `deposit_time`, "
				+ "`deposit_channel`, `bank_account`, `status`,audit_time,audit_name,income_account,content,discount_amount) "
				+ "VALUES (?, ?, ?, ?, ?, ?,?,?,?,?,?)";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return list.size();
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			@SuppressWarnings("static-access")
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, list.get(i).getGameAccount());
				ps.setDouble(2, list.get(i).getDepositAmount());
				ps.setTimestamp(3, Timestamp.valueOf(list.get(i).getDepositTime().now()));
				ps.setInt(4, list.get(i).getDepositChannel());
				ps.setString(5, list.get(i).getBankAccount());
				ps.setInt(6, list.get(i).getStatus());
				ps.setTimestamp(7, Timestamp.valueOf(list.get(i).getAuditTime().now()));
				ps.setString(8, list.get(i).getAuditName());
				ps.setString(9, list.get(i).getIncomeAccount());
				ps.setString(10, list.get(i).getContent());
				ps.setDouble(11, list.get(i).getDiscountAmount());

			}
		});

	}

	// 统计 -- 活动存款 -- 在线 -- 后台添加 ： 为手动添加
	public SumAccountInfo selectStatisticsData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from member_deposit where deposit_type=1 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null)
				sqlitem += " and deposit_time >='" + startTime + "'";

			if (endTime != null)
				sqlitem += " and deposit_time <='" + endTime + "'";
		}
		int count = jdbcTemplate.queryForObject(sql + sqlitem, Integer.class);

		String sql2 = sqlitem + " group by game_account";
		List<Integer> list = jdbcTemplate.query(sql + sql2, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});

		String sqlamount = "select sum(deposit_amount) from member_deposit where deposit_type=1 ";
		Double totalAmount = jdbcTemplate.queryForObject(sqlamount + sqlitem, Double.class);

		SumAccountInfo info = new SumAccountInfo("", count,
				(list == null || list.size() <= 0) == true ? 0 : list.size(), totalAmount == null ? 0 : totalAmount);
		return info;
	}

	// 在线存款
	public SumAccountInfo selectonlineStatisticsData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from member_deposit where deposit_type=2 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null)
				sqlitem += " and deposit_time >='" + startTime + "'";

			if (endTime != null)
				sqlitem += " and deposit_time <='" + endTime + "'";
		}
		int count = jdbcTemplate.queryForObject(sql + sqlitem, Integer.class);

		String sql2 = sqlitem + " group by game_account";
		List<Integer> list = jdbcTemplate.query(sql + sql2, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});

		String sqlamount = "select sum(deposit_amount) from member_deposit where deposit_type=2 ";
		Double totalAmount = jdbcTemplate.queryForObject(sqlamount + sqlitem, Double.class);

		SumAccountInfo info = new SumAccountInfo("", count,
				(list == null || list.size() <= 0) == true ? 0 : list.size(), totalAmount == null ? 0 : totalAmount);
		return info;
	}

	// 在线存款
	public double selectyhchData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select sum(discount_amount) from member_deposit where 1=1 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null)
				sqlitem += " and deposit_time >='" + startTime + "'";

			if (endTime != null)
				sqlitem += " and deposit_time <='" + endTime + "'";
		}
		Double count = jdbcTemplate.queryForObject(sql + sqlitem, Double.class);

		if (count == null)

			return 0d;

		return count;
	}

	// 入款統計

	public int selectComeAmoutCount(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "SELECT count(*) FROM" + "(SELECT bank_account,max(deposit_channelname) AS channel_name,c"
				+ "ount(*) AS count,sum(deposit_amount) AS sum_amount,max(deposit_time) AS new_come_time"
				+ " FROM member_deposit " + "WHERE 1 = 1 ";

		if (startTime == null && endTime == null) {
			sql += " and  TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {

			if (startTime != null && endTime != null) {
				sql += " and deposit_time>='" + endTime + "' and deposit_time<='" + endTime + "'";
			} else {
				sql += " and  TO_DAYS(deposit_time) = TO_DAYS('" + startTime + "')";
			}

		}

		sql += " GROUP BY bank_account) a";

		int i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i;

	}

	public List<PayInmentInfo> selectComeAmoutCountList(PageInfo<PayInmentInfo> page, LocalDateTime startTime,
			LocalDateTime endTime) {

		String sql = "select bank_account,max(deposit_channel) as channel_name,"
				+ "count(*) as count,sum(deposit_amount) as sum_amount,"
				+ "max(deposit_time) as new_come_time from member_deposit where 1=1 ";

		if (startTime == null && endTime == null) {
			sql += " and  TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {

			if (startTime != null && endTime != null) {
				sql += " and deposit_time>='" + startTime + "' and deposit_time<='" + endTime + "'";
			} else {
				sql += " and  TO_DAYS(deposit_time) = TO_DAYS('" + startTime + "')";
			}

		}

		sql += " group by bank_account";
		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();

		sql += " limit ?,?";

		List<PayInmentInfo> list = jdbcTemplate.query(sql, new PayInmentInfo(), start, limit);

		return list;
	}

	public Double selectdepositcountupdate(PayInmentInfo info) {
		String sql = "select sum(deposit_amount) from member_deposit where 1=1 ";

		if (info.getStartTime() == null && info.getEndTime() == null) {
			sql += " and  TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		} else {

			if (info.getStartTime() != null && info.getEndTime() != null) {
				sql += " and deposit_time>='" + info.getStartTime() + "' and deposit_time<='" + info.getEndTime() + "'";
			} else {
				sql += " and  TO_DAYS(deposit_time) = TO_DAYS('" + info.getStartTime() + "')";
			}

		}

		Double d = jdbcTemplate.queryForObject(sql, Double.class);

		return d;

	}

	/**
	 * 出款統計明細
	 * 
	 * @param bankAcount
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public List<DepositInfo> selectdinfoBybankaccount(String bankAcount, LocalDateTime startTime,
			LocalDateTime endTime) {

		String sql = "select * from member_deposit where bank_account='" + bankAcount + "'";

		if (startTime == null && endTime == null) {
			sql += " and TO_DAYS(deposit_time)=TO_DAYS('" + LocalDateTime.now() + "')";
		} else {
			if (startTime != null && endTime == null) {
				sql += " and TO_DAYS(deposit_time)=TO_DAYS('" + startTime + "')";
			}
			if (startTime != null && endTime != null) {
				sql += " and deposit_time>='" + startTime + "' and deposit_time<='" + endTime + "'";
			}
		}
		sql += " order by deposit_time desc";
		List<DepositInfo> list = jdbcTemplate.query(sql, new DepositInfo());

		return list;

	}

	/**
	 * 当天入款
	 * 
	 * @return
	 */
	public Double selectcurtoDayInamount(ReflectInfo info) {

		String sql = "select sum(deposit_amount) from member_deposit where 1=1";

		if ((info.getStartTime() == null || "".equals(info.getStartTime()))) {
			sql += " and  TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		}

		if ((info.getStartTime() != null || !"".equals(info.getStartTime()))
				&& (info.getEndTime() == null || "".equals(info.getEndTime()))) {
			sql += " and  TO_DAYS(deposit_time) = TO_DAYS(NOW())";
		}

		if (info.getStartTime() != null && !"".equals(info.getStartTime())
				&& (info.getEndTime() != null && !"".equals(info.getEndTime()))) {
			sql += " and deposit_time>='" + info.getStartTime() + "' and deposit_time<='" + info.getEndTime() + "'";

		}

		Double d = jdbcTemplate.queryForObject(sql, Double.class);

		if (d == null)
			return Double.valueOf(0.00);
		return d;
	}

}
